
using Youz.Common.DBUtility;
using Youz.Common.Entity;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Youz.Common.DAL
{
    /// <summary>
    /// Sys_AdminInfo Data Class
    /// </summary>
    public partial class Sys_AdminInfoDAL : BaseDAL
    {
        private DbHelperSQL DbHelperSQL = new DbHelperSQL();
        public string TableName = "Sys_AdminInfo";

        public Sys_AdminInfoDAL()
        { }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsByUserNameAndEmail(string MemberAccount, string Email)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Sys_AdminInfo");
            strSql.Append(" where MemberAccount=@MemberAccount and Email=@Email");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberAccount", SqlDbType.NVarChar,100)	,
                    new SqlParameter("@Email", SqlDbType.NVarChar,100)};
            parameters[0].Value = MemberAccount;
            parameters[1].Value = Email;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 重置密码
        /// </summary>
        public bool ResetPwd(string MemberAccount, string Pwd, string SecondPwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update  MemberBasicInfo ");
            strSql.Append(" set  LoginPwd=@Pwd,SecondPwd=@SecondPwd ");
            strSql.Append(" where MemberAccount=@MemberAccount");
            SqlParameter[] parameters = {
					                   new SqlParameter("@MemberAccount", SqlDbType.NVarChar,100),
                                        new SqlParameter("@Pwd", SqlDbType.NVarChar,100),
	                                     new SqlParameter("@SecondPwd", SqlDbType.NVarChar,100)};
            parameters[0].Value = MemberAccount;
            parameters[1].Value = Pwd;
            parameters[2].Value = SecondPwd;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
        }

        #region  成员方法
        /// <summary>
        ///  新增一条数据
        /// </summary>
        public bool Add(Sys_AdminInfo model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into [Sys_AdminInfo](");
            strSql.Append("[MemberAccount],[LoginPwd],[SecondPwd],[FirstName],[LastName],[Sex],[Email],[MobileNo],[Memo],[Status],[IsModify],[Creator],[CreateTime]");
            strSql.Append(") values (");
            strSql.Append("@MemberAccount,@LoginPwd,@SecondPwd,@FirstName,@LastName,@Sex,@Email,@MobileNo,@Memo,@Status,@IsModify,@Creator,@CreateTime");
            strSql.Append(")");

            SqlParameter[] parameters = {
                  
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@LoginPwd", model.LoginPwd),
                    new SqlParameter("@SecondPwd", model.SecondPwd),
                    new SqlParameter("@FirstName", model.FirstName),
                    new SqlParameter("@LastName", model.LastName),
                    new SqlParameter("@Sex", model.Sex),
                    new SqlParameter("@Email", model.Email),
                    new SqlParameter("@MobileNo", model.MobileNo),
                    new SqlParameter("@Memo", model.Memo),
                    new SqlParameter("@Status", model.Status),
                    new SqlParameter("@IsModify", model.IsModify),
                    new SqlParameter("@Creator", model.Creator),
                    new SqlParameter("@CreateTime", model.CreateTime)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  保存一条数据
        /// </summary>
        public bool Update(Sys_AdminInfo model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update [Sys_AdminInfo] set ");
            strSql.Append("[LoginPwd]=@LoginPwd,[SecondPwd]=@SecondPwd,[FirstName]=@FirstName,[LastName]=@LastName,[Sex]=@Sex,[Email]=@Email,[MobileNo]=@MobileNo,[Memo]=@Memo,[Status]=@Status,[IsModify]=@IsModify,[Creator]=@Creator,[CreateTime]=@CreateTime");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {
                  
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@LoginPwd", model.LoginPwd),
                    new SqlParameter("@SecondPwd", model.SecondPwd),
                    new SqlParameter("@FirstName", model.FirstName),
                    new SqlParameter("@LastName", model.LastName),
                    new SqlParameter("@Sex", model.Sex),
                    new SqlParameter("@Email", model.Email),
                    new SqlParameter("@MobileNo", model.MobileNo),
                    new SqlParameter("@Memo", model.Memo),
                    new SqlParameter("@Status", model.Status),
                    new SqlParameter("@IsModify", model.IsModify),
                    new SqlParameter("@Creator", model.Creator),
                    new SqlParameter("@CreateTime", model.CreateTime)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        ///</summary>
        public bool Delete(String MemberAccount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [Sys_AdminInfo] ");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {
			
                new SqlParameter("@MemberAccount", MemberAccount)             
              
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Sys_AdminInfo Get(String MemberAccount)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select  top 1 * from [Sys_AdminInfo] ");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {
			
                new SqlParameter("MemberAccount", MemberAccount)             
              
            };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Sys_AdminInfo Get(DataRow row)
        {
            if (row == null) return null;

            Sys_AdminInfo model = new Sys_AdminInfo();


            if (row["MemberAccount"] != DBNull.Value)
            {
                model.MemberAccount = (String)row["MemberAccount"];
            }

            if (row["LoginPwd"] != DBNull.Value)
            {
                model.LoginPwd = (String)row["LoginPwd"];
            }

            if (row["SecondPwd"] != DBNull.Value)
            {
                model.SecondPwd = (String)row["SecondPwd"];
            }

            if (row["FirstName"] != DBNull.Value)
            {
                model.FirstName = (String)row["FirstName"];
            }

            if (row["LastName"] != DBNull.Value)
            {
                model.LastName = (String)row["LastName"];
            }

            if (row["Sex"] != DBNull.Value)
            {
                model.Sex = (Boolean)row["Sex"];
            }

            if (row["Email"] != DBNull.Value)
            {
                model.Email = (String)row["Email"];
            }

            if (row["MobileNo"] != DBNull.Value)
            {
                model.MobileNo = (String)row["MobileNo"];
            }

            if (row["Memo"] != DBNull.Value)
            {
                model.Memo = (String)row["Memo"];
            }

            if (row["Status"] != DBNull.Value)
            {
                model.Status = (Int32)row["Status"];
            }

            if (row["IsModify"] != DBNull.Value)
            {
                model.IsModify = (Boolean)row["IsModify"];
            }    

            if (row["Creator"] != DBNull.Value)
            {
                model.Creator = (String)row["Creator"];
            }

            if (row["CreateTime"] != DBNull.Value)
            {
                model.CreateTime = (DateTime)row["CreateTime"];
            }


            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet Gets(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from [Sys_AdminInfo] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet Gets(int Top, string sqlWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" *  FROM [Sys_AdminInfo] ");

            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM [Sys_AdminInfo] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获得数据列表分页
        /// </summary>
        public DataTable Gets(string sqlWhere, int pageindex, int pagesize, out int total, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" SELECT a.*,b.RoleID,c.RoleName FROM [Sys_AdminInfo] a ");
            strSql.Append(" left join [Right_MemberRole] b ON  b.MemberAccount=a.MemberAccount and b.[Type]=1 ");
            strSql.Append(" left join [Right_Roleinfo] c ON c.RoleID=b.RoleID ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", "order by CreateTime desc ", out count, cmdParms);
            total = count;
            return dt;
        }

        /// <summary>
        /// 获得数据列表分页
        /// </summary>
        public DataTable Gets(string sqlWhere, int pageindex, int pagesize, out int total, string sort, string order, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            #region sql
            strSql.Append(@"  SELECT  [sJOB].[job_id] AS [ID] ,  
                               [sJOB].[name] AS [Name] ,  
                      CASE WHEN [sJOBH].[run_date] IS NULL  
                  OR [sJOBH].[run_time] IS NULL THEN NULL  
                     ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '  
                  + STUFF(STUFF(RIGHT('000000'  
                                      + CAST([sJOBH].[run_time] AS VARCHAR(6)),  
                                      6), 3, 0, ':'), 6, 0, ':') AS DATETIME)  
                        END AS [RecentExecutionTime] ,  
                        CASE [sJOBH].[run_status]  
                     WHEN 0 THEN '失败'  
                     WHEN 1 THEN '成功'  
                    WHEN 2 THEN '重试'  
                    WHEN 3 THEN '取消'  
                    WHEN 4 THEN '正在运行' -- In Progress  
                  END AS [Status] ,  
                  STUFF(STUFF(RIGHT('000000'  
                          + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3,  
                    0, ':'), 6, 0, ':') AS [LastRunDuration] ,  
                 [sJOBH].[message] AS [message] ,  
                 CASE [sJOBSCH].[NextRunDate]  
                      WHEN 0 THEN NULL  
                            ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' '  
               + STUFF(STUFF(RIGHT('000000'  
                                   + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  
                                   6), 3, 0, ':'), 6, 0, ':') AS DATETIME)  
                    END AS NextRunTime  
                    FROM    [msdb].[dbo].[sysjobs] AS [sJOB]  
                 LEFT JOIN ( SELECT  [job_id] ,  
                            MIN([next_run_date]) AS [NextRunDate] ,  
                            MIN([next_run_time]) AS [NextRunTime]  
                    FROM    [msdb].[dbo].[sysjobschedules]  
                    GROUP BY [job_id]  
                  ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]  
                       LEFT JOIN ( SELECT  [job_id] ,  
                            [run_date] ,  
                            [run_time] ,  
                            [run_status] ,  
                            [run_duration] ,  
                            [message] ,  
                            ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber  
                    FROM    [msdb].[dbo].[sysjobhistory]  
                    WHERE   [step_id] = 0  
                  ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]  
                                  AND [sJOBH].[RowNumber] = 1   -- WHERE [sJOB].[name] = 'job_GC_5MinKLine'
                            --  ORDER BY [RecentExecutionTime] desc 
                   ");
            #endregion
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", " order by  " + sort + "  " + order, out count, cmdParms);
            total = count;
            return dt;
        }

        /// <summary>
        /// 获取作业名称
        /// </summary>
        /// <returns></returns>
        public DataSet GetJobName()
        {
            string sql = @"SELECT  '' AS ID,'ALL' AS jobName
                            UNION ALL
                            SELECT name AS ID,name AS jobName FROM [msdb].[dbo].[sysjobs]";
            DataSet ds = DbHelperSQL.Query(sql);
            return ds;
        }

        /// <summary>
        /// 判断帐号+二级密码是否正確
        /// </summary>
        public bool AdminISSecondPwd(string MemberAccount, string SecondPwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from MemberBasicInfo");
            strSql.Append(" where MemberAccount=@MemberAccount ");
            strSql.Append(" and SecondPwd=@SecondPwd ");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberAccount", SqlDbType.NVarChar,100),
                    new SqlParameter("@SecondPwd", SqlDbType.NVarChar,100)	};
            parameters[0].Value = MemberAccount;
            parameters[1].Value = SecondPwd;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        #endregion  成员方法
    }
}


